package com.symria.server.dao.ad;

import java.util.Date;
import java.util.List;

import com.symria.framework.dao.util.TemplateDao;
import com.symria.framework.page.Page;
import com.symria.server.po.AdAppReport;
import com.symria.server.po.AdAreaReport;
import com.symria.server.po.AdCampaign;
import com.symria.server.po.SysAreaInfo;
import com.symria.system.statement.Statement;
public class AdDao extends TemplateDao {
	public void searchAdCampaign(AdCampaign ad,String viewType,Integer userId,Page page){
		Statement stms = stmsFactory.createStatement("select ad,ac from AdCampaign ad,AdContent ac where ad.adId=ac.adId and ad.userId=?");
		          
		          stms.addParam(userId);
		          if(ad!=null){
		        	  stms.append("and", "ad.status", "=", ad.getStatus());
		        	  stms.appendBetween("and", "ad.createDate", ad.getBeginDate(), ad.getEndDate());
		        	  stms.appendLike(null, "and", "ad.adName", ad.getAdName());
		        	  stms.append("and", "ad.adCate", "=", ad.getAdCate());
		          }
		          stms.append("and", "ac.viewType", "=", viewType);
		          stms.addOrderBy(" ad.createDate desc");
		          
		          
		          templateDao.search(stms, page);
	}
	
	public void reportByDate(Date beginDate,Date endDate,Integer userId,Page page){
		Statement stms = stmsFactory.createNativeStatement(" SELECT aar.CREATE_TIME,SUM(aar.REQUEST_COUNT),SUM(aar.SHOW_COUNT),SUM(aar.CLICK_COUNT),SUM(aar.ACTION_COUNT) FROM AD_APP_REPORT aar,AD_CAMPAIGN ac where aar.AD_ID=ac.AD_ID AND ac.USER_ID=? ");
				  stms.addParam(userId);
		          stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);
				  stms.addGroupBy(" aar.CREATE_TIME");
				  stms.addOrderBy(" aar.CREATE_TIME desc ");
		templateDao.search(stms, page);
	}
	
	//广告id 广告名称  请求数 展示数 点击数 效果数
	public void reportByAd(AdAppReport aar,Date beginDate,Date endDate,Integer userId,Page page){
		  Statement stms = stmsFactory.createNativeStatement(" SELECT aar.AD_ID,ac.AD_NAME,SUM(aar.REQUEST_COUNT) rc,SUM(aar.SHOW_COUNT),SUM(aar.CLICK_COUNT),SUM(aar.ACTION_COUNT) FROM AD_APP_REPORT aar ,AD_CAMPAIGN ac where aar.AD_ID=ac.AD_ID AND ac.USER_ID=?");
		            stms.addParam(userId);
				  if(null!=aar){
						//stms.append("and", "aar.PUBLIC_ID", "=", aar.getPublicId());
						stms.append("and", "aar.AD_ID", "=", aar.getAdId());
						stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);
				   } 
		  stms.appendBetween("and", "aar.CREATE_TIME", beginDate, endDate);
		  stms.addGroupBy(" aar.AD_ID");
		  stms.addOrderBy(" aar.CREATE_TIME desc, rc desc");
		  templateDao.search(stms, page);
		
	}
	public void reportByAdArea(AdAreaReport aar,Date beginDate,Date endDate,Integer userId,Integer  areaId,Page page){
		Statement stms = stmsFactory.createStatement(" select r from AdAreaReport r,AdCampaign ad where r.adId=ad.adId and ad.userId=?");
		          stms.addParam(userId);
		          stms.appendBetween("and", "r.createDate", beginDate, endDate);
		          stms.append("and", "r.areaId", "=", areaId);
		          //stms.appendLike(null,"and", "ad.adName", adName);
		          stms.addOrderBy("r.createDate desc,r.requestCount desc");
		          templateDao.search(stms, page);
	}
	public void reportByApp(AdAppReport aar,Date beginDate,Date endDate,Integer userId,String adName,Page page){
		   Statement stms = stmsFactory.createStatement("select aar from AdAppReport aar,AdCampaign ad,AppInfo ai where aar.appId=ai.appId and  aar.adId=ad.adId and ad.userId=?");
           stms.addParam(userId);
           stms.appendBetween("and", "aar.createTime", beginDate, endDate);
           stms.appendLike(null, "and", "ad.adName", adName);
           stms.addOrderBy("aar.createTime desc,aar.requestCount desc");
		   templateDao.search(stms, page);
	}
	public List<SysAreaInfo> queryAreaInfo(Integer userId){

		          Statement s = stmsFactory.createNativeStatement("SELECT a.* FROM SYS_AREA_INFO a WHERE a.ID IN (SELECT DISTINCT aar.AREA_ID  FROM AD_AREA_REPORT aar ,AD_CAMPAIGN ad WHERE aar.AD_ID=ad.AD_ID AND ad.USER_ID=?)");
		          s.addParam(userId);
		          s.addEntity("a", SysAreaInfo.class);
		          System.out.println(s.toString());
		return templateDao.query(s);
	}
}
